package scfg.output.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map.Entry;

import scfg.AlignedSequence;
import scfg.KHGrammar;
import scfg.RnaAlgoPrediction;
import scfg.output.Display;

public class DatabaseHandler {
	
	private static Connection makeConnection(DatabaseLogin login) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://" + login.getHostname() + "/" + login.getDbname();
			return DriverManager.getConnection(url, login.getUsername(), login.getPassword());
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (NullPointerException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	private static void breakConnection(Connection con){
			try {
				con.close();
			} catch (SQLException e) {
			} catch (NullPointerException e) {
			}
	}
	
	public static boolean insertKHGrammar(KHGrammar grammar) {
		Connection con = makeConnection(new DatabaseLogin());
		boolean rtn = insertKHGrammar(grammar, con);
		breakConnection(con);
		return rtn;
	}
	
	private static boolean insertKHGrammar(KHGrammar grammar, Connection con){
		if (grammar == null || con == null)
			return false;
		try {
			Statement s = con.createStatement();
			String[] queries = GrammarSqlGenerator.KnudsenHeinSqlInsert(grammar);
			// Structure
			if(s.executeUpdate(queries[0], Statement.RETURN_GENERATED_KEYS) > 0){
				ResultSet rs = s.getGeneratedKeys();
				int strid = -1;
				if(rs.next())
					strid = rs.getInt(1);
				
				// Altered Structure
				s.executeUpdate(queries[1], Statement.RETURN_GENERATED_KEYS);
				rs = s.getGeneratedKeys();
				int astrid = -1;
				if(rs.next())
					astrid = rs.getInt(1);
				
				// State Probabilities (including altered)
				s.executeUpdate(queries[2], Statement.RETURN_GENERATED_KEYS);
				rs = s.getGeneratedKeys();
				int stpid = -1;
				if(rs.next())
					stpid = rs.getInt(1);
				
				// Knudsen-Hein Information
				if (strid >= 0 && astrid >= 0 && stpid >= 0){
					queries[3] = queries[3].replace("%strid%", "" + strid);
					queries[3] = queries[3].replace("%astrid%", "" + astrid);
					queries[3] = queries[3].replace("%stpid%", "" + stpid);
					s.executeUpdate(queries[3]);
					
					return true;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public static boolean AlignedSequenceExists(AlignedSequence alnseq) {
		Connection con = DatabaseHandler.makeConnection(new DatabaseLogin());
		boolean rtn = AlignedSequenceExists(alnseq, con);
		breakConnection(con);
		return rtn;
	}
	
	public static boolean AlignedSequenceExists(AlignedSequence alnseq, Connection con) {
		Statement s;
		// TODO;
//		return false;
		try {
			s = con.createStatement();
			ResultSet rs;
			if (alnseq.getAlnseqid() != null) {
				rs = s.executeQuery("SELECT * FROM `RnaSeqDB`.`alnseqs` WHERE `alnseqs`.`alnseqid`="+alnseq.getAlnseqid()+";");
				rs.last();
				if (rs.getRow() > 0)
					return true;
			}
			if (alnseq.getName() != null) {
				rs = s.executeQuery("SELECT * FROM `RnaSeqDB`.`seqinfo` WHERE `seqinfo`.`name`='"+alnseq.getName()+"';");
				rs.last();
				if (rs.getRow() > 0)
					return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	public static int insertAlignedSequence(AlignedSequence alnseq) {
		Connection con = makeConnection(new DatabaseLogin());
		int rtn = insertAlignedSequence(alnseq, con);
		breakConnection(con);
		return rtn;
	}
	
	private static int insertAlignedSequence(AlignedSequence alnseq, Connection con){
		if (alnseq == null || con == null)
			return -99;
		if (AlignedSequenceExists(alnseq))
			return -98;
		try {
			Statement s = con.createStatement();
			String[] queries = GrammarSqlGenerator.AlignedSequenceSqlInsert(alnseq);
			
			// Sequence Info
			if(s.executeUpdate(queries[0], Statement.RETURN_GENERATED_KEYS) > 0){
				ResultSet rs = s.getGeneratedKeys();
				int infoid = -1;
				if(rs.next())
					infoid = rs.getInt(1);
				
				// Sequences
				s.executeUpdate(queries[1], s.RETURN_GENERATED_KEYS);
				rs = s.getGeneratedKeys();
				int seqid = -1;
				if(rs.next())
					seqid = rs.getInt(1);
				
				// Filter Values
				s.executeUpdate(queries[2], Statement.RETURN_GENERATED_KEYS);
				rs = s.getGeneratedKeys();
				int filid = -1;
				if(rs.next())
					filid = rs.getInt(1);
				
				// Aligned Sequence Reference Table
				if (infoid >= 0 && seqid >= 0 && filid >= 0){
					queries[3] = queries[3].replace("%infoid%", "" + infoid);
					queries[3] = queries[3].replace("%seqid%", "" + seqid);
					queries[3] = queries[3].replace("%filid%", "" + filid);
					s.executeUpdate(queries[3], Statement.RETURN_GENERATED_KEYS);
					rs = s.getGeneratedKeys();
					if(rs.next()){
						alnseq.setInfoid(infoid);
						alnseq.setSeqid(seqid);
						alnseq.setFilid(filid);
						alnseq.setAlnseqid(rs.getInt(1));
						updatePredictions(alnseq, con);
						updateAccessionNumbers(alnseq, con);
						return alnseq.getAlnseqid();
					}
					return -1;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
	
	public static boolean updateAlignedSequence(AlignedSequence alnseq) {
		Connection con = makeConnection(new DatabaseLogin());
		boolean rtn = updateAlignedSequence(alnseq, con);
		breakConnection(con);
		return rtn;
	}
	
	private static boolean updateAlignedSequence(AlignedSequence alnseq, Connection con){
		if (alnseq == null || con == null || alnseq.getAlnseqid() == null || alnseq.getInfoid() == null || alnseq.getFilid() == null)
			return false;
		try {
			Statement s = con.createStatement();
			String[] queries = GrammarSqlGenerator.AlignedSequenceSqlUpdate(alnseq);
			
			boolean rtn = true;
			for(String q : queries) {
				rtn &= s.execute(q);
			}
			updatePredictions(alnseq, con);
			return rtn;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public static boolean updatePredictions(AlignedSequence alnseq) {
		Connection con = makeConnection(new DatabaseLogin());
		boolean rtn = updatePredictions(alnseq, con);
		breakConnection(con);
		return rtn;
	}
	
	private static boolean updatePredictions(AlignedSequence alnseq, Connection con){
		if (alnseq == null || con == null || alnseq.getAlnseqid() == null || alnseq.getInfoid() == null || alnseq.getFilid() == null)
			return false;
		try {
			Statement s = con.createStatement();
			HashMap<String, RnaAlgoPrediction> queries = GrammarSqlGenerator.PredictionSqlUpdate(alnseq);
			
			boolean rtn = true;
			for(Entry<String, RnaAlgoPrediction> q : queries.entrySet()) {
				if (q.getValue() == null) {
					rtn &= s.execute(q.getKey());
				} else {
					rtn &= s.executeUpdate(q.getKey(), Statement.RETURN_GENERATED_KEYS) > 0;
					ResultSet rs = s.getGeneratedKeys();
					if(rs.next())
						q.getValue().setPredid(rs.getInt(1));
					else {
						// There is a problem
					}
				}
				
			}
			return rtn;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	private static boolean updateAccessionNumbers(AlignedSequence alnseq, Connection con){
		if (alnseq == null || con == null || alnseq.getAlnseqid() == null || alnseq.getInfoid() == null || alnseq.getFilid() == null)
			return false;
		try {
			Statement s = con.createStatement();
			HashMap<String, DBAccessionNumberPair> queries = GrammarSqlGenerator.AccessionNumberSqlUpdate(alnseq);
			
			boolean rtn = true;
			for(Entry<String, DBAccessionNumberPair> q : queries.entrySet()) {
				if (q.getValue() == null) {
					rtn &= s.execute(q.getKey());
				} else {
					rtn &= s.executeUpdate(q.getKey(), Statement.RETURN_GENERATED_KEYS) > 0;
					ResultSet rs = s.getGeneratedKeys();
					if(rs.next())
						q.getValue().setAccessionNumber(rs.getInt(1)+"");
					else {
						// There is a problem
					}
				}
				
			}
			return rtn;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public static AlignedSequence getAlignedSequence(int alnseqid) {
		Connection con = makeConnection(new DatabaseLogin());
		AlignedSequence rtn = getAlignedSequence(alnseqid, con);
		breakConnection(con);
		return rtn;
	}
	
	private static AlignedSequence getAlignedSequence(int alnseqid, Connection con) {
		if(alnseqid <= 0 || con == null)
			throw new IllegalArgumentException();
		try {
			Statement s = con.createStatement();
			StringBuilder sql_seq = new StringBuilder("SELECT * FROM `RnaSeqDB`.`alnseqs`, `RnaSeqDB`.`seqinfo`, `RnaSeqDB`.`filtervals`, `RnaSeqDB`.`sequences` WHERE `alnseqs`.`alnseqid`=");
			sql_seq.append(alnseqid);
			sql_seq.append(" AND `alnseqs`.`infoid`=`seqinfo`.`infoid` AND `alnseqs`.`filid`=`filtervals`.`filid` AND `alnseqs`.`seqid`=`sequences`.`seqid`;");
			ResultSet rs = s.executeQuery(sql_seq.toString());
			if (rs.next())
				return new AlignedSequence(getAlignedValues(rs), getPrediction(alnseqid, con), getAccessionNums(alnseqid, con));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static List<AlignedSequence> getAlignedSequenceByRNAClass(String rnaClass) {
		return getFilteredAlignedSequences("`seqinfo`.`class`='" + rnaClass + "'");
	}
	
	public static List<AlignedSequence> getFilteredAlignedSequences(String sqlConstraints) {
		Connection con = makeConnection(new DatabaseLogin());
		List<AlignedSequence> rtn = getFilteredAlignedSequences(sqlConstraints, con);
		breakConnection(con);
		return rtn;
	}
	
	private static List<AlignedSequence> getFilteredAlignedSequences(String sqlConstraints, Connection con) {
		if(sqlConstraints == null || con == null)
			throw new IllegalArgumentException();
		List<AlignedSequence> seqList = new LinkedList<AlignedSequence>();
		try {
			Statement s = con.createStatement();
//			StringBuilder sql_seq = new StringBuilder("SELECT * FROM `RnaSeqDB`.`alnseqs`, `RnaSeqDB`.`seqinfo`, `RnaSeqDB`.`filtervals`, `RnaSeqDB`.`sequences`, `RnaSeqDB`.`structures`, `RnaSeqDB`.`accessionnumbers` WHERE `alnseqs`.`infoid`=`seqinfo`.`infoid` AND `alnseqs`.`filid`=`filtervals`.`filid` AND `alnseqs`.`seqid`=`sequences`.`seqid` AND `alnseqs`.`alnseqid`=`structures`.`alnseqid` AND `alnseqs`.`alnseqid`=`accessionnumbers`.`alnseqid` ");
			StringBuilder sql_seq = new StringBuilder("SELECT * FROM `RnaSeqDB`.`alnseqs`, `RnaSeqDB`.`seqinfo`, `RnaSeqDB`.`filtervals`, `RnaSeqDB`.`sequences`, `RnaSeqDB`.`accessionnumbers` WHERE `alnseqs`.`infoid`=`seqinfo`.`infoid` AND `alnseqs`.`filid`=`filtervals`.`filid` AND `alnseqs`.`seqid`=`sequences`.`seqid` AND `alnseqs`.`alnseqid`=`accessionnumbers`.`alnseqid` ");
			if (!sqlConstraints.matches(" ?AND.*") && sqlConstraints.length() > 10)
				sql_seq.append(" AND ");
			sql_seq.append(sqlConstraints);
			if (!sqlConstraints.endsWith(";"))
				sql_seq.append(";");
//			System.out.println(sql_seq.toString());
			ResultSet rs = s.executeQuery(sql_seq.toString());
			while (rs.next()){
				HashMap<String, String> values = getAlignedValues(rs);
				try {
					seqList.add(new AlignedSequence(values, getPrediction(Integer.parseInt(values.get("alnseqid"))), getAccessionNums(Integer.parseInt(values.get("alnseqid")), con)));
				} catch (NumberFormatException e) {
					// Do Nothing
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return seqList;
	}
	
	private static HashMap<String, String> getAlignedValues(ResultSet rs_seq) {
		if (rs_seq == null)
			throw new IllegalArgumentException();
		HashMap<String, String> values = new HashMap<String, String>();
		try {
			values.put("name", rs_seq.getString("name"));
			values.put("rnaClass", rs_seq.getString("class"));
			values.put("rnaSubClass", rs_seq.getString("subclass"));
			values.put("sequence", rs_seq.getString("sequence"));
			values.put("alignment", rs_seq.getString("alignment"));
			values.put("duplicate", rs_seq.getString("duplicate"));
			values.put("inAlignment", rs_seq.getString("inalignment"));
			values.put("dense", rs_seq.getString("dense"));
			values.put("denseComp", rs_seq.getString("densecomp"));
			values.put("percIdentity", rs_seq.getString("percentidentity"));
			values.put("alnseqid", rs_seq.getString("alnseqid"));
			values.put("seqid", rs_seq.getString("seqid"));
			values.put("filid", rs_seq.getString("filid"));
			values.put("infoid", rs_seq.getString("infoid"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return values;
	}
	
	private static HashMap<String, RnaAlgoPrediction> getPrediction(int alnseqid) {
		Connection con = makeConnection(new DatabaseLogin());
		HashMap<String, RnaAlgoPrediction> rtn = getPrediction(alnseqid, con);
		breakConnection(con);
		return rtn;
	}
	
	private static HashMap<String, RnaAlgoPrediction> getPrediction(int alnseqid, Connection con) {
		if (alnseqid <= 0)
			throw new IllegalArgumentException();
		HashMap<String, RnaAlgoPrediction> predictions = new HashMap<String, RnaAlgoPrediction>();
		try {
			Statement s = con.createStatement();
			StringBuilder sql_pred = new StringBuilder("SELECT * FROM `RnaSeqDB`.`alnseqs`, `RnaSeqDB`.`structures` WHERE `alnseqs`.`alnseqid`=");
			sql_pred.append(alnseqid);
			sql_pred.append(" AND `alnseqs`.`alnseqid`=`structures`.`alnseqid`;");
			ResultSet rs = s.executeQuery(sql_pred.toString());
			while(rs.next()) {
				int predid = rs.getInt("predid");
				String algo = rs.getString("algo");
				String structure = rs.getString("structure");
				if (predid > 0 && structure != null)
					predictions.put(algo, new RnaAlgoPrediction(algo, structure, predid));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return predictions;
	}
	
	private static List<DBAccessionNumberPair> getAccessionNums(int alnseqid, Connection con) {
		if (alnseqid <= 0)
			throw new IllegalArgumentException();
		List<DBAccessionNumberPair> accNums = new LinkedList<DBAccessionNumberPair>();
		try {
			Statement s = con.createStatement();
			StringBuilder sql_pred = new StringBuilder("SELECT * FROM `RnaSeqDB`.`alnseqs`, `RnaSeqDB`.`accessionnumbers` WHERE `alnseqs`.`alnseqid`=");
			sql_pred.append(alnseqid);
			sql_pred.append(" AND `alnseqs`.`alnseqid`=`accessionnumbers`.`alnseqid`;");
			ResultSet rs = s.executeQuery(sql_pred.toString());
			while(rs.next()) {
				int accid = rs.getInt("accid");
				String accessionnumber = rs.getString("accessionnumber");
				if (accid > 0 && accessionnumber != null)
					accNums.add(new DBAccessionNumberPair(accessionnumber, accid));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return accNums;
	}
	
	public static List<AlignedSequence> getSequencesByAccessionNumber(String accNum) {
		Connection con = makeConnection(new DatabaseLogin());
		List<AlignedSequence> rtnList = getSequencesByAccessionNumber(accNum, con);
		breakConnection(con);
		return rtnList;
	}
	
	public static List<AlignedSequence> getSequencesByAccessionNumber(String accNum, Connection con) {
		try {
			if (accNum != null && con != null && !con.isClosed())
				return getFilteredAlignedSequences("`accessionnumbers`.`accessionnumber`='"+accNum+"'");
		} catch (SQLException e) {
			// TODO: delete or handle better
			e.printStackTrace();
		}
		return new LinkedList<AlignedSequence>();
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
//		testInsertAlnSeq();
//		testInsertAlnSeq2();
//		testInsertAlnSeq3();
//		testUpdateAlnSeq();
//		testGetAlnSeq();
//		AlignedSequence alnseq = getAlignedSequence(1);
//		alnseq.removeStructure("MFE");
//		updateAlignedSequence(alnseq);
		testGetAllAlnSeq();
		System.out.println("Done");
	}
	
	public static void testInsertKH(){
		DatabaseLogin login = new DatabaseLogin();
		Connection con = makeConnection(login);
		if (con != null){
			insertKHGrammar(new KHGrammar("Test Run 2", new Display("DatabaseHandler")), con);
		}
		breakConnection(con);
	}
	
	public static void testInsertAlnSeq(){
		DatabaseLogin login = new DatabaseLogin("DBconfig_SEQ.ini");
		Connection con = makeConnection(login);
		if (con != null){
			insertAlignedSequence(new AlignedSequence(), con);
		}
		breakConnection(con);
	}
	
	public static void testInsertAlnSeq2(){
		DatabaseLogin login = new DatabaseLogin("DBconfig_SEQ.ini");
		Connection con = makeConnection(login);
		if (con != null){
			AlignedSequence alnseq = new AlignedSequence();
			alnseq.setName("Test123");
			insertAlignedSequence(alnseq, con);
		}
		breakConnection(con);
	}
	
	public static void testInsertAlnSeq3(){
		DatabaseLogin login = new DatabaseLogin("DBconfig_SEQ.ini");
		Connection con = makeConnection(login);
		if (con != null){
			AlignedSequence alnseq = new AlignedSequence();
			List<DBAccessionNumberPair> accessionNumbers =new LinkedList<DBAccessionNumberPair>();
			accessionNumbers.add(new DBAccessionNumberPair("test123456"));
			alnseq.setAccessionNumber(accessionNumbers);
			alnseq.setAlignment("--CC--CCAAAAG-GGG");
			alnseq.setDense(0.875);
			alnseq.setDenseComp(0.05);
			alnseq.setDuplicate(false);
			alnseq.setInAlignment(true);
			alnseq.setName("test123456");
			alnseq.setRnaClass("5S");
			alnseq.setRnaSubClass("protobacterial");
			alnseq.setSequence("CCCCAAAAGGGG");
			alnseq.addStructure(new RnaAlgoPrediction("native", "((((....))))"));
			alnseq.addStructure(new RnaAlgoPrediction("refrence", "((((....))))"));
			alnseq.addStructure(new RnaAlgoPrediction("MFE", ".(((....)).)"));
			insertAlignedSequence(alnseq, con);
		}
		breakConnection(con);
	}
	
	public static void testUpdateAlnSeq(){
		DatabaseLogin login = new DatabaseLogin("DBconfig_SEQ.ini");
		Connection con = makeConnection(login);
		if (con != null){
			AlignedSequence alnseq = new AlignedSequence();
			insertAlignedSequence(alnseq, con);
			List<DBAccessionNumberPair> accessionNumbers =new LinkedList<DBAccessionNumberPair>();
			accessionNumbers.add(new DBAccessionNumberPair("test123456"));
			alnseq.setAccessionNumber(accessionNumbers);
			alnseq.setAlignment("--CC--CCAAAAG-GGG");
			alnseq.setDense(0.875);
			alnseq.setDenseComp(0.05);
			alnseq.setDuplicate(false);
			alnseq.setInAlignment(true);
			alnseq.setName("test123456");
			alnseq.setRnaClass("5S");
			alnseq.setRnaSubClass("protobacterial");
			alnseq.setSequence("CCCCAAAAGGGG");
			alnseq.addStructure(new RnaAlgoPrediction("native", "((((....))))"));
			alnseq.addStructure(new RnaAlgoPrediction("refrence", "((((....))))"));
			alnseq.addStructure(new RnaAlgoPrediction("MFE", ".(((....)).)"));
			updateAlignedSequence(alnseq);
		}
		breakConnection(con);
	}
	
	public static void testGetAlnSeq() {
		AlignedSequence alnseq = getAlignedSequence(1);
		System.out.println(alnseq);
	}
	
	public static void testGetAllAlnSeq() {
		List<AlignedSequence> seqs = getFilteredAlignedSequences("");
		for (AlignedSequence seq : seqs) {
			System.out.println(seq + "\n");
		}
	}
}
